package com.ecsolutions.dao;

import com.ecsolutions.entity.Guarantee_Entity;
import com.ecsolutions.entity.Loan;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.ResultType;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;
import org.springframework.stereotype.Repository;

import java.util.List;

/**
 * Created by Administrator on 2017/8/22.
 */
@Repository
public interface LoanDao {
    @Select("select " +
            "loanref as refno," +
            "custcod ," +
            "lineno as crline," +
            "contractsymbol as contrano," +
            "to_char(signeddate,'YYYY-MM-DD') as contradt," +
            "dircflag1 as menlei," +
            "dircflag2 as dalei," +
            "dircflag3 as zhonglei ," +
            "dircflag4 as xiaolei," +
            "accoffic as accoff," +
            "accofname ," +
            "schetype as SCHTYP," +
            "to_char(drawdate,'YYYY-MM-DD') as dwndate," +
            "drawccy as lnccy," +
            "drawamt as lnamt," +
            "paymentmethod rpymth," +
            "tenor as instno," +
            "fixflag as inttype," +
            "ratetype as ratetyp," +
            "payfreq as rpyfeq," +
            "payper as rpyper," +
            "to_char(firstpaydate,'YYYY-MM-DD') as wpaydat," +
            "rate," +
            "spread," +
            "percent as percenta," +
            "schemarate as wtotrat," +
            "overdurate as wtotort," +
            "collflag as collfalg FROM Utilizationdetail where bpm_no=#{bpm_no}")
    @ResultType(Loan.class)
    Loan getPopupupByLoanrefFromUtilizationdetail(String bpm_no);

    //取门类/大类/中类/小类信息
    //取门类信息
    @Select("select leibiemingcheng from hangye where menlei=#{menleiId} and dalei is null and zhonglei is null and xiaolei is null" )
    String getMenlei(String menleiId);
    //取大类信息
    @Select("select leibiemingcheng from hangye where menlei=#{arg0} and dalei=#{arg1} and zhonglei is null and xiaolei is null")
    String getDalei(String menleiId,String daleiId);
    //取中类信息
    @Select("select leibiemingcheng from hangye where menlei=#{arg0} and dalei=#{arg1} and zhonglei =#{arg2} and xiaolei is null")
    String getZhonglei(String menleiId,String daleiId,String zhongleiId);
   //取小类信息
    @Select("select leibiemingcheng from hangye where menlei=#{arg0} and dalei=#{arg1} and zhonglei =#{arg2} and xiaolei =#{arg3}")
    String getXiaolei(String menleiId,String daleiId,String zhongleiId,String xiaoleiId);

    @Select("select CUFULNM as cufulnm,CUSADD1 as wkcuad1,CUSADD2 as wkcuad2, CUSADD3 as wkcuad3 FROM Hkbbimas where custcod=(select distinct custcod from Utilizationdetail where loanref=(select loanref from Utilizationdetail where bpm_no = #{bpm_no} and rownum=1))and rownum=1")
    @ResultType(Loan.class)
    Loan getPopupupByLoanrefFromHKBBIMAS(String bpm_no);

    @Select("select userid,branch FROM tblusers where userid=#{userid}")
    @ResultType(Loan.class)
    Loan getPopupupByUseridFromTBLUESRS(String userid);

    @Select("select AVG(instamt) as instamt ,sum(intamt) as wtotins FROM paymentSchedule  where loanref=(select loanref from Utilizationdetail where bpm_no = #{bpm_no} and rownum=1)" )
    @ResultType(Loan.class)
    Loan getPopupupByLoanrefFrompaymentSchedule (String bpm_no);


    @Select("select dueonhl,stlord1,stlord2,stlord3,stlord4,stlord5,latechr as latchgs,penlchr as penchgs,grace,prtrps ,intbase from hkbinsch   where SCHETYP=(select schetype from Utilizationdetail where loanref=(select loanref from Utilizationdetail where bpm_no = #{bpm_no} and rownum=1))")
    @ResultType(Loan.class)
    Loan getPopupupByloanreffFromHKBINSCH (String bpm_no);


    @Select("select margin ,leftpurchaseprice as lgamt FROM Contractinformation  where loanId=(select loanref from Utilizationdetail where bpm_no = #{bpm_no} and rownum=1)")
    @ResultType(Loan.class)
    Loan getPopupupByloanIdFromContractinformation (String bpm_no);

    @Select("select countryrisk as country FROM loanapplicantfacilityinfo   where bpm_no=#{bpm_no}")
    @ResultType(Loan.class)
    Loan getPopupupByloanIdFromloanapplicantfacilityinfo (String bpm_no);


    @Insert("Insert into LORINCAN (refno,custcod,cufulnm,wkcuad1,wkcuad2,wkcuad3,branch,bankent,relref,crline,contrano,contradt,menlei,dalei,zhonglei,xiaolei,country,accoff,schtyp,dwndate,lnccy,lnamt,rpymth,instno,instamt,inttype,ratetyp,rpyfeq,rpyper,wpaydat,matdate,rate,spread,percenta,wtotrat,wtotort,intcalf,collfalg,dueonhl,stlord1,stlord2,stlord3,stlord4,stlord5,latchgs,penchgs,grace,margin,lgamt,intbase,fun,wtotins,prtrps,fliuser,flidate,flitime)" +
            "values(" +
            "#{refno,jdbcType=VARCHAR}," +
            "#{custcod}," +
            "#{cufulnm}," +
            "#{wkcuad1,jdbcType=VARCHAR}," +
            "#{wkcuad2,jdbcType=VARCHAR}," +
            "#{wkcuad3,jdbcType=VARCHAR}," +
            "#{branch}," +
            "substr(#{branch},1,1)," +
            "#{relref,jdbcType=VARCHAR}," +
            "#{crline}," +
            "#{contrano}," +
            "to_date(#{contradt},'YYYY-MM-DD')," +
            "#{menlei}," +
            "#{dalei}," +
            "#{zhonglei}," +
            "#{xiaolei}," +
            "#{country}," +
            "#{accoff}," +
            "#{schtyp}," +
            "to_date(#{dwndate},'YYYY-MM-DD')," +
            "#{lnccy}," +
            "#{lnamt}," +
            "#{rpymth}," +
            "#{instno}," +
            "#{instamt}," +
            "#{inttype}," +
            "#{ratetyp}," +
            "#{rpyfeq}," +
            "#{rpyper}," +
            "to_date(#{wpaydat},'YYYY-MM-DD')," +
            "to_date(#{matdate},'YYYY-MM-DD')," +
            "#{rate}," +
            "#{spread}," +
            "#{percenta}," +
            "#{wtotrat}," +
            "#{wtotort}," +
            "#{intcalf}," +
            "#{collfalg}," +
            "#{dueonhl}," +
            "#{stlord1}," +
            "#{stlord2}," +
            "#{stlord3}," +
            "#{stlord4}," +
            "#{stlord5}," +
            "#{latchgs}," +
            "#{penchgs}," +
            "#{grace}," +
            "#{margin}," +
            "#{lgamt,jdbcType=VARCHAR}," +
            "#{intbase}," +
            "'hkbincan'," +
            "#{wtotins}," +
            "#{prtrps}," +
            "#{userid}," +
            "(select to_char(sysdate,'yyyyMMdd') from dual)," +
            "(select to_char(sysdate,'hh24miss') from dual))")
    void saveLoan(Loan loan);


    @Select("select refno,custcod,cufulnm,wkcuad1,wkcuad2,wkcuad3,branch,bankent,relref,crline,lntype,contrano,to_char(contradt,'yyyy-MM-dd') as contradt,menlei,dalei,zhonglei,xiaolei,country,accoff,schtyp,to_char(dwndate,'yyyy-MM-dd') as dwndate,lnccy,lnamt,rpymth,instno,instamt,inttype,ratetyp,rpyfeq,rpyper,to_char(wpaydat,'yyyy-MM-dd') as wpaydat,to_char(matdate,'yyyy-MM-dd') as matdate,rate,spread,percenta,wtotrat,wtotort,intcalf,collfalg,dueonhl,stlord1,stlord2,stlord3,stlord4,stlord5,latchgs,penchgs,grace,margin,lgamt,intbase,fun,wtotins,prtrps from lorincan where REFNO=(select loanref from Utilizationdetail where bpm_no = #{bpm_no} and rownum=1)")
    Loan getLoanByBpm_no(String bpm_no);


    @Update("Update lorincan set AUSER=#{arg1},ADATE=(select to_char(sysdate,'yyyyMMdd') from dual),func=(select to_char(sysdate,'hh24miss') from dual) where REFNO=(select loanref from Utilizationdetail where bpm_no = #{arg0} and rownum=1)")
    void savaLoanReview(String bpm_no,String userid);




}
